How to export table data into a SQL insert script?
Have you ever had a table that you wanted to drop and recreate but you need all the data inside it. Perhaps it took you a lot of time to generate this data.
Well, you can save the data out as a SQL insert script if you want. Here is how:
- Open Microsoft SQL Server Management Studio and login.
- Right-click on the database and choose Tasks | Generate Scripts.
Note: The first time you do this you will see and Introduction page. Click Next. You can click the check box next to Do not show this page again and you won’t see this screen. - On the Choose Objects screen, click Select specific database objects.
- Expand Tables and click the check box next to the table you want to save and click Next.
- On the Set Scripting Options screen, click Advanced.
- Scroll down and under Types of data to script select Data only and click OK.
- Under File name select a file name to save to and click Next.
- Click Next, Next, Finish.
You now have a script to insert your data.
Urgh! That's why I prefer MySQL. Just mysqldump -h HOST -u user -p -database DATABASE > database.sql
and voilá! There it is our sql (structure + data). Just one command instead of 8 steps and 8 windows 😛
I used to use MySQL a lot in the early 2000's and I loved the simplicity of mysqldump. I wonder if there is a simple command for MS SQL and I just don't know it.